# import psycopg2
# import pandas as pd
# import os
# import time

# # 数据库连接参数
# connection_params = {
#     'dbname': 'imdb_database',
#     'user': 'postgres',
#     'password': '0818',
#     'host': 'localhost',
#     'port': '5432'
# }

# def connect_to_db(params):
#     try:
#         connection = psycopg2.connect(**params)
#         print("成功连接到数据库")
#         return connection
#     except Exception as e:
#         print(f"连接数据库失败: {e}")
#         return None

# def load_queries_from_file(file_path):
#     try:
#         with open(file_path, 'r', encoding='utf-8') as f:
#             queries = f.read().split(";\n")
#         queries = [q.strip() for q in queries if q.strip()]  # 移除空行
#         print(f"从文件中加载了 {len(queries)} 条查询语句")
#         return queries
#     except Exception as e:
#         print(f"加载查询语句失败: {e}")
#         return []

# def update_dataframe_with_explain(df, queries, connection):
#     results = []
#     with connection.cursor() as cursor:
#         for idx, query in enumerate(queries):
#             start_time = time.time()
#             try:
#                 cursor.execute(f"EXPLAIN {query}")
#                 explain_output = cursor.fetchall()
#                 generation_time = time.time() - start_time
#                 validation_result = 'Passed'
#             except Exception as e:
#                 explain_output = str(e)
#                 generation_time = time.time() - start_time
#                 validation_result = 'Failed'

#             results.append({
#                 "Query ID": idx + 1,
#                 "Generated SQL Query": query,
#                 "Generation Time": generation_time,
#                 "Validation Result": validation_result,
#                 "EXPLAIN Output": str(explain_output) if validation_result == "Passed" else "",
#                 "Error Message": str(explain_output) if validation_result == "Failed" else "",
#                 "Prompt": '',  # 这里根据需要修改
#                 "LLM Response": query,
#                 "Explanation Failure Reason": str(explain_output) if validation_result == "Failed" else ""
#             })
    
#     return pd.DataFrame(results)

# def create_empty_dataframe():
#     columns = [
#         'Query ID',
#         'Generated SQL Query',
#         'Generation Time',
#         'Validation Result',
#         'EXPLAIN Output',
#         'Error Message',
#         'Prompt',
#     ]
#     df = pd.DataFrame(columns=columns)
#     return df

# def save_dataframe_to_file(df, file_path):
#     df.to_excel(file_path, index=False)
#     print(f"查询记录已保存到 {file_path}")

# def main():
#     file_path = "C:\\Users\\Aphro\\Desktop\\query_log02.xlsx"
    
#     # 连接到数据库
#     conn = connect_to_db(connection_params)

#     if conn:
#         # 从文件中加载查询语句
#         queries = load_queries_from_file("D:\\database\\queries1.sql")

#         if not os.path.isfile(file_path):
#             print(f"文件不存在，创建新的文件: {file_path}")
#             df = create_empty_dataframe()
#             save_dataframe_to_file(df, file_path)
        
#         # 更新 DataFrame
#         df = update_dataframe_with_explain(pd.DataFrame(columns=create_empty_dataframe().columns), queries, conn)

#         # 保存更新的 DataFrame 到 Excel 文件
#         save_dataframe_to_file(df, file_path)

#         conn.close()
#         print("数据库连接已关闭")

# if __name__ == "__main__":
#     main()


# # import psycopg2
# # from openai import OpenAI
# # import openai 
# # import time

# # client = OpenAI(
# #     api_key="sk-FB9VFg4kAJfs4qCDLlQ10XRxJeNVzNIenLDJw8V9YqH1gzRF",
# #     base_url="https://api.chatanywhere.tech/v1"
# # )

# # # 数据库连接参数
# # connection_params = {
# #     'dbname': 'imdb_database',
# #     'user': 'postgres',
# #     'password': '0818',
# #     'host': 'localhost',
# #     'port': '5432'
# # }

# # def connect_to_db(params):
# #     try:
# #         connection = psycopg2.connect(**params)
# #         print("成功连接到数据库")
# #         return connection
# #     except Exception as e:
# #         print(f"连接数据库失败: {e}")
# #         return None

# # def generate_query_prompt():
# #     prompt = """
# # Based on the following table structure, generate a nested SQL query considering conditions and constraints, including subqueries. 
# # Ensure that the returned SQL query consists of SQL code only, without any comments or explanations.
# # The generated query must comply with SQL syntax and be executable in the database.

#     # table structure：
#     # 1. aka_name 表：
#     #    - id：整数，非空，主键
#     #    - person_id：整数，非空
#     #    - name：可变字符类型
#     #    - imdb_index：可变字符类型(3)
#     #    - name_pcode_cf：可变字符类型(11)
#     #    - name_pcode_nf：可变字符类型(11)
#     #    - surname_pcode：可变字符类型(11)
#     #    - md5sum：可变字符类型(65)

#     # 2. aka_title 表：
#     #    - id：整数，非空，主键
#     #    - movie_id：整数，非空
#     #    - title：可变字符类型
#     #    - imdb_index：可变字符类型(4)
#     #    - kind_id：整数，非空
#     #    - production_year：整数
#     #    - phonetic_code：可变字符类型(5)
#     #    - episode_of_id：整数
#     #    - season_nr：整数
#     #    - episode_nr：整数
#     #    - note：可变字符类型(72)
#     #    - md5sum：可变字符类型(32)

#     # 3. cast_info 表：
#     #    - id：整数，非空，主键
#     #    - person_id：整数，非空
#     #    - movie_id：整数，非空
#     #    - person_role_id：整数
#     #    - note：可变字符类型
#     #    - nr_order：整数
#     #    - role_id：整数，非空

#     # 4. char_name 表：
#     #    - id：整数，非空，主键
#     #    - name：可变字符类型，非空
#     #    - imdb_index：可变字符类型(2)
#     #    - imdb_id：整数
#     #    - name_pcode_nf：可变字符类型(5)
#     #    - surname_pcode：可变字符类型(5)
#     #    - md5sum：可变字符类型(32)

#     # 5. comp_cast_type 表：
#     #    - id：整数，非空，主键
#     #    - kind：可变字符类型(32)，非空

#     # 6. company_name 表：
#     #    - id：整数，非空，主键
#     #    - name：可变字符类型，非空
#     #    - country_code：可变字符类型(6)
#     #    - imdb_id：整数
#     #    - name_pcode_nf：可变字符类型(5)
#     #    - name_pcode_sf：可变字符类型(5)
#     #    - md5sum：可变字符类型(32)

#     # 7. company_type 表：
#     #    - id：整数，非空，主键
#     #    - kind：可变字符类型(32)

#     # 8. complete_cast 表：
#     #    - id：整数，非空，主键
#     #    - movie_id：整数
#     #    - subject_id：整数，非空
#     #    - status_id：整数，非空

#     # 9. info_type 表：
#     #    - id：整数，非空，主键
#     #    - info：可变字符类型(32)，非空

#     # 10. keyword 表：
#     #     - id：整数，非空，主键
#     #     - keyword：可变字符类型，非空
#     #     - phonetic_code：可变字符类型(5)

#     # 11. kind_type 表：
#     #     - id：整数，非空，主键
#     #     - kind：可变字符类型(15)

#     # 12. link_type 表：
#     #     - id：整数，非空，主键
#     #     - link：可变字符类型(32)，非空

#     # 13. movie_companies 表：
#     #     - id：整数，非空，主键
#     #     - movie_id：整数，非空
#     #     - company_id：整数，非空
#     #     - company_type_id：整数，非空
#     #     - note：可变字符类型

#     # 14. movie_info_idx 表：
#     #     - id：整数，非空，主键
#     #     - movie_id：整数，非空
#     #     - info_type_id：整数，非空
#     #     - info：可变字符类型，非空
#     #     - note：可变字符类型(1)

#     # 15. movie_keyword 表：
#     #     - id：整数，非空，主键
#     #     - movie_id：整数，非空
#     #     - keyword_id：整数，非空

#     # 16. movie_link 表：
#     #     - id：整数，非空，主键
#     #     - movie_id：整数，非空
#     #     - linked_movie_id：整数，非空
#     #     - link_type_id：整数，非空

#     # 17. name 表：
#     #     - id：整数，非空，主键
#     #     - name：可变字符类型，非空
#     #     - imdb_index：可变字符类型(9)
#     #     - imdb_id：整数
#     #     - gender：可变字符类型(1)
#     #     - name_pcode_cf：可变字符类型(5)
#     #     - name_pcode_nf：可变字符类型(5)
#     #     - surname_pcode：可变字符类型(5)
#     #     - md5sum：可变字符类型(32)

#     # 18. role_type 表：
#     #     - id：整数，非空，主键
#     #     - role：可变字符类型(32)，非空

#     # 19. title 表：
#     #     - id：整数，非空，主键
#     #     - title：可变字符类型，非空
#     #     - imdb_index：可变字符类型(5)
#     #     - kind_id：整数，非空
#     #     - production_year：整数
#     #     - imdb_id：整数
#     #     - phonetic_code：可变字符类型(5)
#     #     - episode_of_id：整数
#     #     - season_nr：整数
#     #     - episode_nr：整数
#     #     - series_years：可变字符类型(49)
#     #     - md5sum：可变字符类型(32)

#     # 20. movie_info 表：
#     #     - id：整数，非空，主键
#     #     - movie_id：整数，非空
#     #     - info_type_id：整数，非空
#     #     - info：可变字符类型，非空
#     #     - note：可变字符类型

#     # 21. person_info 表：
#     #     - id：整数，非空，主键
#     #     - person_id：整数，非空
#     #     - info_type_id：整数，非空
#     #     - info：可变字符类型，非空
#     #     - note：可变字符类型
# #     """
# #     return prompt

# # def save_queries_to_file(queries, file_path):
# #     try:
# #         with open(file_path, 'w', encoding='utf-8') as f:
# #             for query in queries:
# #                 f.write(query + ";\n")
# #         print(f"查询语句已保存到 {file_path}")
# #     except Exception as e:
# #         print(f"保存查询语句失败: {e}")

# # def generate_query(prompt):
# #     response = client.chat.completions.create(
# #         messages=[
# #             {
# #                 "role": "user", 
# #                 "content": prompt
# #             }
# #         ],
# #         moedl = "gpt-4",
# #     )
#     # return response.choices[0].message.content.strip()

# # def main():
# #     # 连接到数据库
# #     conn = connect_to_db(connection_params)

# #     if conn:
# #         # 创建模型 Prompt
# #         prompt = generate_query_prompt()

# #         # 记录生成查询的开始时间
# #         start_time = time.time()

# #         # 批量生成查询语句
# #         queries = []
# #         for i in range(20): 
# #             print(f"正在生成第 {i+1} 条查询语句")
# #             query = generate_query(prompt)
# #             if query:
# #                 queries.append(query)

# #         # 记录生成查询的结束时间
# #         end_time = time.time()
# #         print(f"生成查询语句所用时间: {end_time - start_time}秒")

# #         # 保存查询语句到文件
# #         print("正在保存查询语句到文件")
# #         save_queries_to_file(queries, "D:\\database\\queries5.sql")

# #         # 使用 EXPLAIN 验证查询语句的正确性
# #         with conn.cursor() as cursor:
# #             for query in queries:
# #                 try:
# #                     cursor.execute(f"EXPLAIN {query}")
# #                     print(f"查询语句正确: {query}")
# #                 except Exception as e:
# #                     print(f"查询语句错误: {query}\n错误信息: {e}")

# #         # 关闭数据库连接
# #         conn.close()
# #         print("数据库连接已关闭")

# # if __name__ == "__main__":
# #     main()










import re

def analyze_sql(sql):
    # 正则表达式匹配表名
    table_pattern = re.compile(r'\b(FROM|JOIN)\s+([\w\.]+)', re.IGNORECASE)
    # 正则表达式匹配JOIN操作
    join_pattern = re.compile(r'\bJOIN\b', re.IGNORECASE)

    # 查找所有表名
    tables = set(re.findall(table_pattern, sql))
    # 计算JOIN操作的数量
    joins = len(re.findall(join_pattern, sql))

    # 计算表的数量，如果FROM和JOIN中有不同的表名，它们应该被计算在内
    table_count = len(tables)

    return table_count, joins

# 从用户那里获取SQL语句
sql_input = input("请输入您的SQL语句：")
tables, joins = analyze_sql(sql_input)

print(f"SQL语句涉及的表的数量：{tables}")
print(f"SQL语句中的JOIN操作数量：{joins}")